package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.jhc.bean.AllHfmsgBean;
import cn.jhc.bean.ShowmvBean;


public class ShowmvDAO {
	private Connection con = null;
	int PageCount,YesNum;
	int AgreedNum;
	int MsgCount;
	public ShowmvBean getOneTitle(){
		ShowmvBean s = new ShowmvBean();
		String sql = "select id,imagename,title,agreed,against,date from uploadmv where star='yes' order by id desc";
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps=con.prepareStatement(sql);
			result=ps.executeQuery();
			if(!result.next()){
				s=null;
			}else{
				s.setId(result.getInt(1));
				s.setImagename(result.getString(2));
				s.setTitle(result.getString(3));
				s.setAgreed(result.getInt(4));
				s.setAgainst(result.getInt(5));
				s.setDate(result.getDate(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return s;
	}
	public ArrayList<ShowmvBean> getAllTitle(int page,int newsCount){
		ArrayList<ShowmvBean> list = new ArrayList<ShowmvBean>();
		String sql = "select id,imagename,title,agreed,against,date from uploadmv where star='no' order by id desc limit "
				+ (page-1) * (newsCount) + "," + newsCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			while(result.next()) {
				ShowmvBean s = new ShowmvBean();
				s.setId(result.getInt(1));
				s.setImagename(result.getString(2));
				s.setTitle(result.getString(3));
				s.setAgreed(result.getInt(4));
				s.setAgainst(result.getInt(5));
				s.setDate(result.getDate(6));
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList<ShowmvBean> getAllYesTitle(int page,int newsCount){
		ArrayList<ShowmvBean> list = new ArrayList<ShowmvBean>();
		String sql = "select id,imagename,title,agreed,against,date from uploadmv where star='yes' order by id desc limit "
				+ (page-1) * (newsCount) + "," + newsCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			while(result.next()) {
				ShowmvBean s = new ShowmvBean();
				s.setId(result.getInt(1));
				s.setImagename(result.getString(2));
				s.setTitle(result.getString(3));
				s.setAgreed(result.getInt(4));
				s.setAgainst(result.getInt(5));
				s.setDate(result.getDate(6));
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public ShowmvBean getOneflash(int id){
		ShowmvBean s = new ShowmvBean();
		String sql = "select id,flashname,title,context,agreed,against,date from uploadmv where id= "+id+"";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			if (!result.next())
				s = null;
			else {
				s.setId(result.getInt(1));
				s.setFlashname(result.getString(2));
				s.setTitle(result.getString(3));
				s.setContext(result.getString(4));
				s.setAgreed(result.getInt(5));
				s.setAgainst(result.getInt(6));
				s.setDate(result.getDate(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return s;
	}
	
	public int getCount(int newsCount) {
		String sql = "select count(*) from uploadmv";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%newsCount==0){
					this.PageCount=(result.getInt(1)/newsCount);	
				}else{
					this.PageCount=(result.getInt(1)/newsCount+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}	
	
	public int getYesNum(int id) {
		String sql = "select agreed from uploadmv where id="+id+"";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				this.YesNum=result.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return YesNum ;
	}	
	public void UpdateAgreed(int i ,int id){
		String sql = "update uploadmv set agreed=? where id= ? ";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, i);
			st.setInt(2, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public int getNoNum(int id) {
		String sql = "select against from uploadmv where id="+id+"";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				this.YesNum=result.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return YesNum ;
	}	
	public void UpdateAgainst(int i ,int id){
		String sql = "update uploadmv set against=? where id= ? ";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, i);
			st.setInt(2, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void addHfMessage(int userid, String content, int uploadmv_id) {
		String sql = "insert into uploadmsg(use_id,content,uploadmv_id,date) "
				+ "values(?,?,?,now())";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, userid);
			st.setString(2, content);
			st.setInt(3, uploadmv_id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public ArrayList<AllHfmsgBean> getHFMessages(int id,int page) {
		ArrayList<AllHfmsgBean> List = new ArrayList<AllHfmsgBean>();
		String sql = "select u.id,u.displayname,h.uploadmv_id,h.content,h.date"
				+ " from user as u join uploadmsg as h where h.uploadmv_id=" + id
				+ " and u.id=h.use_id limit "
				+ (page-1) * (10) + "," + 10 + "";
		Statement stat = null;
		ResultSet result = null;
		try {
			stat = con.createStatement();
			result = stat.executeQuery(sql);
			while (result.next()) {
				AllHfmsgBean h = new AllHfmsgBean();
				h.setUserId(result.getInt(1));
				h.setDisplayname(result.getString(2));
				h.setTitleid(result.getInt(3));
				h.setContent(result.getString(4));
				h.setDate(result.getDate(5));
				List.add(h);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return List;
	}
	public void UploadMV(String imagename,String flashname,String title, String context) {
		String sql = "insert into uploadmv(imagename,flashname,title,context,date,star) "
				+ "values(?,?,?,?,now(),'no')";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, imagename);
			st.setString(2, flashname);
			st.setString(3, title);
			st.setString(4, context);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void deleteMV(int id) {
		String sql = "delete from uploadmv where id = ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void deleteHFMV(int id) {
		String sql = "delete from uploadmsg where uploadmv_id = ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public int getMsgCount(int id) {
		String sql = "select count(*) from uploadmsg where uploadmv_id="+id+"";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				this.MsgCount=(result.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return MsgCount ;
	}
	public void UpdateStarYes(int id){
		String sql = "update uploadmv set star='yes' where id= ? ";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void UpdateStarNo(int id){
		String sql = "update uploadmv set star='no' where id= ? ";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void setCon(Connection con) {
		this.con = con;
	}
}
